package com.weiqk.pmdemo.test;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.weiqk.pmdemo.dao.DicRepository;
import com.weiqk.pmdemo.pojo.Project;
import com.weiqk.pmdemo.service.DicService;
import com.weiqk.pmdemo.service.ProjectService;
import com.weiqk.pmdemo.service.impl.DicServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
public class test {
    @Autowired
    DicService dicService;
    @Autowired
    DicRepository dicRepository;
    @Autowired
    ProjectService projectService;
    @Test
    public void testExport() throws IOException {
        // 获取全部用户
        Iterable<Project> all = projectService.findAll();
        List<Project> lists = new ArrayList<>();
        all.forEach(single->{lists.add(single);});
        System.out.println(lists);

        // 导出到文件
        FileOutputStream outputStream = new FileOutputStream("B:\\导出.xls");

        // 配置Excel文件信息
        ExportParams params = new ExportParams();
        params.setTitle("导出的项目信息");
        params.setSheetName("项目信息表");

        // 导出Excel表格
        Workbook workbook = ExcelExportUtil.exportExcel(params, Project.class, lists);
        workbook.write(outputStream);

        // 关闭资源
        outputStream.close();
        workbook.close();
    }
    @Test
    public void testImport() throws Exception {
        // 参数1：文件流
        FileInputStream stream = new FileInputStream("B:\\导入.xls");
        // 参数2：导入类型
        ImportParams params = new ImportParams();
        // 标题占用多少行
        params.setTitleRows(1);
        // 头部属性占用多少行
        params.setHeadRows(1);
        // 从指定的sheet的下标开始读取
        // params.setStartSheetIndex(1);
        // 读取sheet的数量，需要和上面的配合
        // params.setSheetNum(1);

        // 对Excle进行合法参数校验
        params.setImportFields(new String[]{"PROJECT_ID"});

        // 参数3：导出的数据结合
        List<Project> projects = ExcelImportUtil.importExcel(stream, Project.class, params);
        for (Project p:projects){
            projectService.put(p);
        }

    }

    /**
     * 将数据库dname中的表名为tname的数据表写入Excel表格
     * @param dname
     * @param tname
     */
    @Test
    public  void writeDbtoExcel(){
        String tname="PM_PROJECT";
        String dname="PM_demo";
        String path="B:/axls/"+tname+".xls";

        HSSFWorkbook book=new HSSFWorkbook();
        HSSFSheet sheet=book.createSheet("表");
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con;
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+dname+"?useUnicode=true&characterEncoding=utf-8","root","qyft664500");
            Statement st=con.createStatement();
            String sql="select * from "+tname;
            ResultSet rs=st.executeQuery(sql);
            ResultSetMetaData rsmd=rs.getMetaData();//得到结果集的字段名
            int c=rsmd.getColumnCount();//得到数据表的结果集的字段的数量
            //生成表单的第一行，即表头
            HSSFRow row0=sheet.createRow(0);//创建第一行
            for(int i=0;i<c;i++){
                HSSFCell cel=row0.createCell(i);//创建第一行的第i列
                cel.setCellValue(rsmd.getColumnName(i+1));
//				cel.setCellStyle(style);
            }
            //将数据表中的数据按行导入进Excel表中
            int r=1;
            while(rs.next()){
                HSSFRow row=sheet.createRow(r++);//创建非第一行的其他行
                for(int i=0;i<c;i++){//仍然是c列，导入第r行的第i列
                    HSSFCell cel=row.createCell(i);
                    //以下两种写法均可
//					cel.setCellValue(rs.getString(rsmd.getColumnName(i+1)));
                    cel.setCellValue(rs.getString(i+1));
                }
            }
            //用文件输出流类创建名为table的Excel表格
            FileOutputStream out=new FileOutputStream(path);
            book.write(out);//将HSSFWorkBook中的表写入输出流中
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
//        return path;
    }
}


